package com.che.software.testato.domain.dao.jdbc.impl;

import java.sql.Connection;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import org.apache.commons.dbutils.DbUtils;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;
import org.apache.log4j.Logger;
import org.springframework.stereotype.Repository;

import com.che.software.testato.domain.dao.IServiceDAO;
import com.che.software.testato.domain.dao.jdbc.adao.AbstractDAO;
import com.che.software.testato.domain.dao.jdbc.exception.ServiceCreationDAOException;
import com.che.software.testato.domain.dao.jdbc.exception.ServiceSearchDAOException;
import com.che.software.testato.domain.entity.Service;
import com.che.software.testato.domain.entity.search.ServiceSearch;

/**
 * JDBC implementation of the DAO interface dedicated to the services
 * management.
 * 
 * @author Clement HELIOU (clement.heliou@che-software.com).
 * @copyright Che Software.
 * @license GNU General Public License.
 * @see AbstractDAO, IServiceDAO.
 * @since July, 2011.
 * 
 *        This file is part of Testato.
 * 
 *        Testato is free software: you can redistribute it and/or modify it
 *        under the terms of the GNU General Public License as published by the
 *        Free Software Foundation, either version 3 of the License, or (at your
 *        option) any later version.
 * 
 *        Testato is distributed in the hope that it will be useful, but WITHOUT
 *        ANY WARRANTY; without even the implied warranty of MERCHANTABILITY or
 *        FITNESS FOR A PARTICULAR PURPOSE. See the GNU General Public License
 *        for more details.
 * 
 *        You should have received a copy of the GNU General Public License
 *        along with Testato. If not, see <http://www.gnu.org/licenses/>.
 * 
 *        Testato's logo is a creation of Arrioch
 *        (http://arrioch.deviantart.com/) and it's distributed under the terms
 *        of the Creative Commons License.
 */
@Repository("serviceDAO")
public class ServiceDAO extends AbstractDAO implements IServiceDAO {

	/**
	 * Constants.
	 */
	private static final Logger LOGGER = Logger.getLogger(ServiceDAO.class);

	/**
	 * Creates a service from his label.
	 * 
	 * @author Clement HELIOU (clement.heliou@che-software.com).
	 * @param label the label of the service to create.
	 * @since July, 2011.
	 * @throws ServiceCreationDAOException if an error occurs during the
	 *         creation.
	 */
	@Override
	public void createServiceFromLabel(String label) throws ServiceCreationDAOException {
		LOGGER.debug("createServiceFromLabel(" + label + ").");
		Connection connection = null;
		try {
			connection = getDataSource().getConnection();
			getQueryRunner().update(connection, "INSERT INTO service(service_id, label) VALUES(nextval('service_seq'), ?) ", new Object[] { label });
		} catch (SQLException e) {
			throw new ServiceCreationDAOException(e);
		} finally {
			if (null != connection) {
				DbUtils.closeQuietly(connection);
			}
		}
	}

	/**
	 * Retrieves the id of the last user hired by a specific service.
	 * 
	 * @author Clement HELIOU (clement.heliou@che-software.com).
	 * @param serviceId the service id.
	 * @return the resulting user id.
	 * @since July, 2011.
	 * @throws ServiceSearchDAOException if an error occurs during the search.
	 */
	@Override
	public int getLastHiredUserIdFromServiceId(int serviceId) throws ServiceSearchDAOException {
		LOGGER.debug("getLastHiredUserIdFromServiceId(" + serviceId + ").");
		Connection connection = null;
		try {
			connection = getDataSource().getConnection();
			Object res = getQueryRunner().query(connection, "SELECT user_id::integer AS userId FROM user_service WHERE service_id = ? ORDER BY hired_date DESC ", new ScalarHandler("userId"), new Object[] { serviceId });
			return (null != res) ? ((Integer) res) : -1;
		} catch (SQLException e) {
			throw new ServiceSearchDAOException(e);
		} finally {
			if (null != connection) {
				DbUtils.closeQuietly(connection);
			}
		}
	}

	/**
	 * Retrieves the users number of a specific service.
	 * 
	 * @author Clement HELIOU (clement.heliou@che-software.com).
	 * @param serviceId the service id.
	 * @return the resulting number.
	 * @since July, 2011.
	 * @throws ServiceSearchDAOException if an error occurs during the search.
	 */
	@Override
	public long getUsersNumberFromServiceId(int serviceId) throws ServiceSearchDAOException {
		LOGGER.debug("getUsersNumberFromServiceId(" + serviceId + ").");
		Connection connection = null;
		try {
			connection = getDataSource().getConnection();
			return (Long) getQueryRunner().query(connection, "SELECT COUNT(*) AS usersNb FROM user_service WHERE service_id = ? ", new ScalarHandler("usersNb"), new Object[] { serviceId });
		} catch (SQLException e) {
			throw new ServiceSearchDAOException(e);
		} finally {
			if (null != connection) {
				DbUtils.closeQuietly(connection);
			}
		}
	}

	/**
	 * Service search from an user id.
	 * 
	 * @author Clement HELIOU (clement.heliou@che-software.com).
	 * @param userId the user id.
	 * @return the resulting object list.
	 * @since July, 2011.
	 * @throws ServiceSearchDAOException if an error occurs during the search.
	 */
	@Override
	public Service searchServiceFromUserId(int userId) throws ServiceSearchDAOException {
		LOGGER.debug("searchServiceFromUserId(" + userId + ").");
		Connection connection = null;
		try {
			connection = getDataSource().getConnection();
			return getQueryRunner().query("SELECT service_id AS serviceId, label FROM user_service JOIN service USING(service_id) WHERE user_id = ? ", new BeanHandler<Service>(Service.class), new Object[] { userId });
		} catch (SQLException e) {
			throw new ServiceSearchDAOException(e);
		} finally {
			if (null != connection) {
				DbUtils.closeQuietly(connection);
			}
		}
	}

	/**
	 * Service search from a bean of criterions.
	 * 
	 * @author Clement HELIOU (clement.heliou@che-software.com).
	 * @param searchBean the criterions to use for the search.
	 * @return the resulting object list.
	 * @since July, 2011.
	 * @throws ServiceSearchDAOException if an error occurs during the search.
	 */
	@Override
	public List<Service> searchService(ServiceSearch searchBean) throws ServiceSearchDAOException {
		LOGGER.debug("searchService().");
		Connection connection = null;
		List<Service> result = null;
		try {
			connection = getDataSource().getConnection();
			List<Object> params = new ArrayList<Object>();
			result = getQueryRunner().query(connection, getServiceSearchQueryFromCriterion(searchBean, params), new BeanListHandler<Service>(Service.class), params.toArray());
		} catch (SQLException e) {
			throw new ServiceSearchDAOException(e);
		} finally {
			if (null != connection) {
				DbUtils.closeQuietly(connection);
			}
		}
		return result;
	}

	/**
	 * Recovery of the service search query from criterion.
	 * 
	 * @author Clement HELIOU (clement.heliou@che-software.com).
	 * @param searchBean the object containing the criterions.
	 * @param params the parameters list corresponding to the built query.
	 * @return the built query.
	 * @since July, 2011.
	 */
	private String getServiceSearchQueryFromCriterion(ServiceSearch searchBean, List<Object> params) {
		LOGGER.debug("getServiceSearchQueryFromCriterion().");
		setWhereClauseEnabled(false);
		StringBuilder sBuilder = new StringBuilder("SELECT service_id AS serviceId, label FROM service ");
		if (null != searchBean && null != searchBean.getServiceId() && 0 != searchBean.getServiceId()) {
			sBuilder.append(getWhereClauseBegin());
			sBuilder.append("service_id = ? ");
			params.add(searchBean.getServiceId());
		}
		return sBuilder.toString();
	}
}